﻿Imports Entidad
Imports System.Data
Imports System.Data.SqlClient
Public Class TurnoDatos

    Function listarTurnos() As DataSet
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()

        Dim query As String = "SELECT TUR_Codigo,TUR_Descripcion " &
                              "FROM Turno " &
                              "WHERE TUR_Activo = '1'"

        Dim adapter As New SqlDataAdapter(query, coneccion)
        Dim ds As New DataSet
        adapter.Fill(ds)

        coneccion.Close()

        Return ds
    End Function

    Function obtenerTurnos(fecha As String) As DataSet
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()

        Dim query As String = "SELECT TUR_Codigo, TUR_Descripcion, '1' AS COL " &
                              "FROM Turno " &
                              "WHERE CAST('" & fecha & "' AS DATETIME) >= TUR_Inicio " &
                              "AND CAST('" & fecha & "' AS DATETIME) < TUR_Fin " &
                              "UNION " &
                              "SELECT TUR_Codigo, TUR_Descripcion, '0' AS COL " &
                              "FROM Turno t1 " &
                              "WHERE t1.TUR_Codigo NOT IN (SELECT t2.TUR_Codigo  " &
                              "FROM Turno t2 " &
                              "WHERE CAST('" & fecha & "' AS DATETIME) >= t2.TUR_Inicio  " &
                              "AND CAST('" & fecha & "' AS DATETIME) < t2.TUR_Fin) " &
                              "ORDER BY COL DESC"

        Dim adapter As New SqlDataAdapter(query, coneccion)
        Dim ds As New DataSet
        adapter.Fill(ds)

        coneccion.Close()

        Return ds
    End Function

End Class
